package com.my.main.base.impnexp;

import com.my.main.base.exception.MyException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


@Component
public class ExcelImpUtil  extends ExcelUtil {
	

	/**
	 * Excel导入工具
	 * @param path	文件路径
	 * @param cls		对象类
	 * @param flag 	该excel文件是否有标题，为null代表无标题
	 * @return	包含该对象类型的list集合
	 * @throws Exception 
	 */
	public <T> List<T> isImpSuccess(String path, Class cls,Integer flag) {
		
		try {
			return readerExcel( path, cls, flag);
		} 
		catch (MyException e) {
			e.printStackTrace();
			logger.error(e);
			return new ArrayList();
		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
			return new ArrayList();
		}
		
	}
	


	/**
	 * 获取类的所有属性名(首字母大写)
	 * @param className		类名
	 * @throws ClassNotFoundException 
	 * @author my
	 * @return 所有属性名的字符串集合
	 */
	private List<String> getFieldUP(String className) throws ClassNotFoundException {
		   List<String> list = new ArrayList<String>();
		   Class clazz =	Class.forName(className);
	       // 获取实体类的所有属性信息，返回属性数组  
	       Field[] fields = clazz.getDeclaredFields();  
	       for (Field field : fields) {  
	    	  String name = field.getName();//获取属性名
	    	  char head = name.charAt(0);
	    	  String header = String.valueOf(head).toUpperCase();
	    	  name = header+name.substring(1);
	    	  list.add(name);
	       }
	       return list;
	 }
	
	
	
	/**
	 * 获取类的所有属性名
	 * @param className				类名
	 * @throws ClassNotFoundException 
	 * @author my
	 * @return 所有属性名的字符串集合
	 */
	private  List<String> getField(String className) throws ClassNotFoundException {
		   List<String> list = new ArrayList<String>();
		   Class clazz =	Class.forName(className);
	       // 获取实体类的所有属性信息，返回属性数组  
	       Field[] fields = clazz.getDeclaredFields();  
	       for (Field field : fields) {  
	    	  String name = field.getName();//获取属性名
	    	  list.add(name);
	       }
	       return list;
	 }
	
    
	
	/**
      * 将一个类创建一个对象
      * @param className	类名
      * @return		对象
      */
	private  Object getObject(String className) throws Exception {
		Class<?> clazz =	Class.forName(className);
		return clazz.newInstance();
	}
	
	
	
	public  String getCellIsDate(HSSFCell cell) {
		DecimalFormat df = new DecimalFormat("#");
		if (cell == null)
		return "";
		switch (cell.getCellTypeEnum()) {
		case NUMERIC:
		if(HSSFDateUtil.isCellDateFormatted(cell)){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
		}
		return df.format(cell.getNumericCellValue());
		case STRING:
		System.out.println(cell.getStringCellValue());
		return cell.getStringCellValue();
		case FORMULA:
		return cell.getCellFormula();
		case BLANK:
		return "";
		case BOOLEAN:
		return cell.getBooleanCellValue() + "";
		case ERROR:
		return cell.getErrorCellValue() + "";
		}
		return "";
	}
	
	
	/**
	 * 判断该反射数据在java中所属类型
	 * @param data
	 * @return
	 * @throws Exception 
	 */
	private  Object getExcelOb(String cls,Object data) throws Exception {
		Object ob = null;
		if (data==null) {
			return null;
		}
		if (cls.endsWith("String") ||cls.endsWith("string") ) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= String.valueOf(data.toString());
			}		
		}
		else if (cls.endsWith("Boolean")||cls.endsWith("boolean")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= Boolean.parseBoolean(data.toString().toLowerCase());
			}		
			
		}
		else if (cls.endsWith("Byte")||cls.endsWith("byte")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= Byte.parseByte(data.toString());
			}		
			
		}
		else if (cls.endsWith("Integer")||cls.endsWith("int")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= (int)Double.parseDouble(data.toString());
			}		
			
		}
		else if (cls.endsWith("Float")||cls.endsWith("float")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= Float.parseFloat(data.toString());
			}		
			
		}
		else if (cls.endsWith("Double")||cls.endsWith("double")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob  = Double.parseDouble(data.toString());
			}		
			
		}
		else if (cls.endsWith("Short")||cls.endsWith("short")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= Short.parseShort(data.toString());
			}		
			
		}
		else if (cls.endsWith("Long")||cls.endsWith("long")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob	= Long.parseLong(data.toString());
			}		
		    
		}
		else if (cls.endsWith("Date")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				if (data instanceof String) {
					ob = new SimpleDateFormat(TIMEFORMAT).parse(data.toString());
				}
				else if (data instanceof Date) {
					ob = data;
				}
				else {
					ob = null;
				}
			}		
			
			
		}
		else if(cls.endsWith("BigDecimal")) {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				Double d = Double.parseDouble(data.toString());
				 ob = new BigDecimal(d).setScale(2, BigDecimal.ROUND_HALF_UP);
			}
		}
		else {
			if(data.equals("(空)")||data.equals("")) {
				ob = null;
			}else {
				ob = data;
			}
		}
		return ob;
	}
	
	
	/**
	 * 判断该excel数据在java中所属类型
	 * 2018 5 30 修改为直接返回为字符串，通过java属性判断类型，不再使用该方法
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public  Object getExcelOb(Cell cell) {
//		DecimalFormat df = new DecimalFormat("#");
		if (cell == null) return "";
		switch (cell.getCellTypeEnum()) {
			case NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat sdf = new SimpleDateFormat(TIMEFORMAT);
					return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
				}
//				return df.format(cell.getNumericCellValue());
				return cell.getNumericCellValue();
			case STRING:
//				System.out.println(cell.getStringCellValue());
				return cell.getStringCellValue();
			case FORMULA:
				return cell.getCellFormula();
			case BLANK:
				return "";
			case BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case ERROR:
				return cell.getErrorCellValue() + "";
		}
		return "";
/*		Object strCell = null;
		c00.setCellType(Cell.CELL_TYPE_STRING);
		strCell = c00.getRichStringCellValue().getString().trim();
		         switch (c00.getCellType()) {
		             case Cell.CELL_TYPE_STRING:
		            	  strCell = c00.getRichStringCellValue().getString().trim();
		                 break;
		             case Cell.CELL_TYPE_NUMERIC:
		            	 if (HSSFDateUtil.isCellDateFormatted(c00)) {
		            		 strCell = c00.getDateCellValue();
		            	 }else {
		            		 strCell = c00.getNumericCellValue();
		            	 }
		                
		                  break;
		             case Cell.CELL_TYPE_BOOLEAN:
		                strCell = c00.getBooleanCellValue();
		                 break;
		             case Cell.CELL_TYPE_BLANK:
		                 strCell = null;
		                 break;
		             default:
		                 strCell =c00.getStringCellValue();
		                 break;
		         }
		        return strCell.toString();*/
	}
	
	/**
	 * 获取该对象的在Excel的集合
	 * @param obj
	 * @return
	  */
	private Map<String, ExcelCell> getCells(Object obj) {
		
        Map<String, ExcelCell> excelCellMap = new HashMap<String, ExcelCell>();
       
        try {
	        // 得到类对象
	        Class clazz = (Class)obj.getClass();
	        // 得到类中的所有属性集合
	        Field[] fileArray = clazz.getDeclaredFields();
	        for (int i=0; i<fileArray.length; i++) {
	            Field field = fileArray[i];
	            field.setAccessible(true); 							// 设置此属性是可以访问的
	            
	            // 有注解读取，没有注解忽略
	            PoExcelAnnotation ann = field.getAnnotation(PoExcelAnnotation.class);
	            if (ann != null) {
	            	Object val = field.get(obj);
	            	ExcelCell cell = new ExcelCell(val, ann.title(), ann.codeType());
	            	excelCellMap.put(field.getName(), cell);		// 设置单元格信息
	            }
	        }
        } 
        catch (IllegalAccessException e) {
			logger.error(e);
			throw new MyException("读取属性数据时出现安全权限参数异常");
        }
        catch (IllegalArgumentException e) {
			logger.error(e);
			throw new MyException("读取属性数据时出现参数异常");
        }
        catch (Exception e) {
        	 e.printStackTrace();
        	logger.error(e);
        	throw new MyException("读取属性数据时出现异常");
        }
        
        return excelCellMap;
        
    }
	
	/**
	 * 获取该对象的在Excel的集合(反，前面是excel名，后面是属性名)
	 * @param obj
	 * @return
	 */
	private	 Map<String,String > getCellsF(Object obj) {
        Map<String,String> excelCellMap = new HashMap<String,String>();
       
        try {
	        // 得到类对象
	        Class clazz = (Class)obj.getClass();
	        // 得到类中的所有属性集合
	        Field[] fileArray = clazz.getDeclaredFields();
	        for (int i=0; i<fileArray.length; i++) {
	            Field field = fileArray[i];
	            field.setAccessible(true); 							// 设置此属性是可以访问
	            // 有注解读取，没有注解忽略
	            PoExcelAnnotation ann = field.getAnnotation(PoExcelAnnotation.class);
	            if (ann != null) {
	            	Object val = field.get(obj);
	            	ExcelCell cell = new ExcelCell(val, ann.title(), ann.codeType());
	            	excelCellMap.put(cell.getTitle(), field.getName());		// 设置单元格信息
	            }
	        }
        } 
        catch (IllegalAccessException e) {
			logger.error(e);
			throw new MyException("读取属性数据时出现安全权限参数异常");
        }
        catch (IllegalArgumentException e) {
			logger.error(e);
			throw new MyException("读取属性数据时出现参数异常");
        }
        catch (Exception e) {
        	 e.printStackTrace();
        	logger.error(e);
        	throw new MyException("读取属性数据时出现异常");
        }
        
        return excelCellMap;
    }
	
	
	
	/**
	 * 读取一个excel文件并读取成list集合
	 * @param url	文件路径
	 * @param cls		对象类
	 * @param flag 	该excel文件是否有标题，为null代表无标题
	 * @return	包含该对象类型的list集合
	 * @throws Exception 
	 */
	public  <T> List<T> readerExcel(String url, Class cls, Integer flag) throws Exception{
		/** 在这判断用户的excel是否第一行是标题如果有标题，flag = 1,else flag= 0**/
		
		Integer index = 0;			//是否有标题
		Integer limit = 2;			//从第几行开始
		if(flag == null) {
			index = -1;
		}
		
		String c = cls.getName();									// 获取类名
		List<T> list = new ArrayList<T>();							// 返回结果集合
		Map<String, Class>  paramcls = getFieldClass(c);			// 获取参数类型
		
		List<String>  excelList = new ArrayList<String>();			// 存储excel单元格中的表头
		Map<String, ExcelCell> mas = getCells(cls.newInstance()); 	// 获取类属性名对应的表头名，
		Map<String, String> masF = getCellsF(cls.newInstance()); 	// 获取表头名对应的类属性名
		if (paramcls.size() == 0 || mas.size() == 0 || masF.size() == 0) {
			return list;
		}
		
		// 获取传入的文件
//		FileInputStream fis = new FileInputStream(Configure.ROOT_REALPATH+path);
		InputStream fis = new URL(url).openStream();
		
		Workbook rwb = null;
		if (url.indexOf(".xlsx") != -1){
			// 支持07版本以前的excel
			rwb = new XSSFWorkbook(fis);
		} 
		else if(url.indexOf(".xls") != -1){
			// 支持07版本以后的excel
			rwb = new HSSFWorkbook(fis);
		}  
		else{
			//不支持文件类型
			return new ArrayList();
		}
		 
		// 获取第一张表
		Sheet sheet = rwb.getSheetAt(0);
		// 获取表头文字
		List<ExcelCell> titleCellList = new ExcelExpUtil().getCellTitle(cls);
		try {
			// 先获取一遍excel中的属性名
			for(int i=0; i<titleCellList.size(); i++) {
				//new  HSSFCell ().getStringCellValue();
				Row c00 = sheet.getRow((limit-1)+index);	 
				excelList.add(c00.getCell(i).getStringCellValue());
			}
			
			// 开始将数据存入list
			while(true) {
				Object ob = getObject(c);		// 为这个对象赋值
				for(int i=0; i<excelList.size(); i++) {	
					// 从第i个横着的，第limit+index个竖着的值开始获取
					//Cell c00 = sheet.getCell(i, limit+index);	 
					Row c00 = sheet.getRow(limit+index);
//					HSSFCell sf = c00.getCell(i);
//					Object result = getExcelOb(sf);
					Field f = ob.getClass().getDeclaredField(titleCellList.get(i).getName().toString());
					f.setAccessible(true);
					// System.out.println(paramcls.get(titleCellList.get(i).getName()).toString());
					Object o = getExcelOb(paramcls.get(titleCellList.get(i).getName()).toString(), getExcelOb(c00.getCell(i)));
					f.set(ob, o);
				
					//excelList.add(result); 
				}
				list.add((T)ob);
				limit++;
			}
		} catch (NullPointerException e) {
			//这里异常是正常情况，是我用来终止无限循环的方式，一旦读取到空单元格就代表没有单元格可以读取了，便会发生异常跳出循环
		} catch(MyException e) {
			e.printStackTrace();
			logger.error(e);
			throw new MyException(e.getMessage());
		}
		catch (FileNotFoundException e) {
			e.printStackTrace();
			logger.error(e);
			throw new MyException("文件读取失败");
		} 
		catch (IOException e) {
			logger.error(e);
			throw new MyException("文件读取时出现流错误");
		}
		catch (Exception e) {
			e.printStackTrace();
			logger.error(e);
			throw new MyException("文件读取时出现异常");
		}
		finally {
		    if (fis != null) {
				try {
					fis.close();
				} 
				catch (IOException e) {
					logger.error(e);
				}
			}
		    if (rwb != null) {
				try {
					rwb.close();
				} 
				catch (Exception e) {
					logger.error(e);
				}
			}
		}
	
		return list;
	}
}